using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using labDict.Web.common;

namespace labDict.Web.provider
{
    public class LabTestDA
    {
        public static void Insert(LabTest tst)
        {
            
            using (IDbConnection con = SQLHelper.GetConnection())
            {
                using (IDbCommand cmd = SQLHelper.CreateCommand(con, "up_LabTest_ins"))
                {
                    decimal dec = 0.00M;

                    IDbDataParameter pTEST_ID = SQLHelper.AddDecimalParameter(cmd, "@TEST_ID", 18, 0, ParameterDirection.Output, dec);
                    
                    SQLHelper.AddStringParameter(cmd, "@TEST", tst.TEST);

                    SQLHelper.AddDecimalParameter(cmd, "@DIVISION_ID", 18, 0, tst.DIVISION_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@FUNC_AREA_ID", 18, 0, tst.FUNC_AREA_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@SPEC_TYPE_ID", 18, 0, tst.SPEC_TYPE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@CONTAINER_ID", 18, 0, tst.CONTAINER_ID);
                    SQLHelper.AddStringParameter(cmd, "@ALT_CONTAINER", tst.ALT_CONTAINER);
                    SQLHelper.AddStringParameter(cmd, "@PATIENT_PREP", tst.PATIENT_PREP);
                    SQLHelper.AddStringParameter(cmd, "@MIN_VOL_WARD", tst.MIN_VOL_WARD);
                    SQLHelper.AddDecimalParameter(cmd, "@ULT_REQ_ID", 18, 0, tst.ULT_REQ_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@DOWN_REQ_ID", 18, 0, tst.DOWN_REQ_ID);
                    SQLHelper.AddStringParameter(cmd, "@SUBMIT_SPEC", tst.SUBMIT_SPEC);
                    SQLHelper.AddStringParameter(cmd, "@INFO_CALL", tst.INFO_CALL);
                    SQLHelper.AddStringParameter(cmd, "@SPEC_HAND", tst.SPEC_HAND);
                    SQLHelper.AddStringParameter(cmd, "@INSTRUMENT", tst.INSTRUMENT);
                    SQLHelper.AddStringParameter(cmd, "@REF_OUT", tst.REF_OUT);
                    SQLHelper.AddStringParameter(cmd, "@TAT_STAT", tst.TAT_STAT);
                    SQLHelper.AddStringParameter(cmd, "@REF_RANGE", tst.REF_RANGE);
                    SQLHelper.AddStringParameter(cmd, "@CRITICAL_VALUE", tst.CRITICAL_VALUE);
                    SQLHelper.AddStringParameter(cmd, "@STORE_TEMP_PRE", tst.STORE_TEMP_PRE);
                    SQLHelper.AddStringParameter(cmd, "@STORE_TEMP_POST", tst.STORE_TEMP_POST);
                    SQLHelper.AddStringParameter(cmd, "@MIN_VOL_LAB", tst.MIN_VOL_LAB);
                    SQLHelper.AddDecimalParameter(cmd, "@PRICE_ESTIMATE", 12, 2, tst.PRICE_ESTIMATE);
                    SQLHelper.AddDecimalParameter(cmd, "@PRICE_UPPER", 12, 2, tst.PRICE_UPPER);
                    SQLHelper.AddStringParameter(cmd, "@CLINICAL_DESCRIPTION", tst.CLINICAL_DESCRIPTION);
                    SQLHelper.AddStringParameter(cmd, "@DOCREFERENCES", tst.DOCREFERENCES);
                    SQLHelper.AddStringParameter(cmd, "@WEB_REF_RANGE", tst.WEB_REF_RANGE);
                    SQLHelper.AddDecimalParameter(cmd, "@REF_CONV_ID", 18, 0, tst.REF_CONV_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@METHOD_ID", 18, 0, tst.METHOD_ID);
                    SQLHelper.AddStringParameter(cmd, "@COMPONENTS", tst.COMPONENTS);
                    SQLHelper.AddDecimalParameter(cmd, "@SHIPPING_ID", 18, 0, tst.SHIPPING_ID);
                    SQLHelper.AddStringParameter(cmd, "@COLLECTION_INSTRUCTIONS", tst.COLLECTION_INSTRUCTIONS);
                    SQLHelper.AddDecimalParameter(cmd, "@TESTING_SCHEDULE_ID", 18, 0, tst.TESTING_SCHEDULE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@TESTING_SCHEDULE2_ID", 18, 0, tst.TESTING_SCHEDULE2_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@WEB_TAT_ID", 18, 0, tst.WEB_TAT_ID);
                    SQLHelper.AddStringParameter(cmd, "@LOINC_CODE", tst.LOINC_CODE);
                    SQLHelper.AddStringParameter(cmd, "@LIS_CODE", tst.LIS_CODE);
                    SQLHelper.AddStringParameter(cmd, "@WEBVOLUME_REC", tst.WEBVOLUME_REC);
                    SQLHelper.AddStringParameter(cmd, "@WEBVOLUME_REQ", tst.WEBVOLUME_REQ);
                    SQLHelper.AddDecimalParameter(cmd, "@INTERNATIONAL_UNIT_ID", 18, 0, tst.INTERNATIONAL_UNIT_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@SPECIMEN_TYPE_ID", 18, 0, tst.SPECIMEN_TYPE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@WEBDISPLAY", 38, 0, tst.WEBDISPLAY);
                    SQLHelper.AddDecimalParameter(cmd, "@WEBDISCLAIMER_ID", 18, 0, tst.WEBDISCLAIMER_ID);
                    SQLHelper.AddStringParameter(cmd, "@SYNONYM1", tst.SYNONYM1);
                    SQLHelper.AddStringParameter(cmd, "@SYNONYM2", tst.SYNONYM2);
                    //SQLHelper.AddStringParameter(cmd, "@SOURCE", tst.SOURCE);
                    SQLHelper.AddDecimalParameter(cmd, "@DISPLAYINTERNAL", 38, 0, tst.DISPLAYINTERNAL);
                    SQLHelper.AddDecimalParameter(cmd, "@GRANTPRICE", 12, 2, tst.GRANTPRICE);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    tst.TEST_ID = Decimal.Parse(pTEST_ID.Value.ToString());
                }
            }
        }

        public static void Update(LabTest tst)
        {

            using (IDbConnection con = SQLHelper.GetConnection())
            {
                using (IDbCommand cmd = SQLHelper.CreateCommand(con, "up_LabTest_modify"))
                {
                    decimal dec = tst.TEST_ID;

                    IDbDataParameter pTEST_ID = SQLHelper.AddDecimalParameter(cmd, "@TEST_ID", 18, 0, ParameterDirection.Input, dec);

                    SQLHelper.AddStringParameter(cmd, "@TEST", tst.TEST);

                    SQLHelper.AddDecimalParameter(cmd, "@DIVISION_ID", 18, 0, tst.DIVISION_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@FUNC_AREA_ID", 18, 0, tst.FUNC_AREA_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@SPEC_TYPE_ID", 18, 0, tst.SPEC_TYPE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@CONTAINER_ID", 18, 0, tst.CONTAINER_ID);
                    SQLHelper.AddStringParameter(cmd, "@ALT_CONTAINER", tst.ALT_CONTAINER);
                    SQLHelper.AddStringParameter(cmd, "@PATIENT_PREP", tst.PATIENT_PREP);
                    SQLHelper.AddStringParameter(cmd, "@MIN_VOL_WARD", tst.MIN_VOL_WARD);
                    SQLHelper.AddDecimalParameter(cmd, "@ULT_REQ_ID", 18, 0, tst.ULT_REQ_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@DOWN_REQ_ID", 18, 0, tst.DOWN_REQ_ID);
                    SQLHelper.AddStringParameter(cmd, "@SUBMIT_SPEC", tst.SUBMIT_SPEC);
                    SQLHelper.AddStringParameter(cmd, "@INFO_CALL", tst.INFO_CALL);
                    SQLHelper.AddStringParameter(cmd, "@SPEC_HAND", tst.SPEC_HAND);
                    SQLHelper.AddStringParameter(cmd, "@INSTRUMENT", tst.INSTRUMENT);
                    SQLHelper.AddStringParameter(cmd, "@REF_OUT", tst.REF_OUT);
                    SQLHelper.AddStringParameter(cmd, "@TAT_STAT", tst.TAT_STAT);
                    SQLHelper.AddStringParameter(cmd, "@REF_RANGE", tst.REF_RANGE);
                    SQLHelper.AddStringParameter(cmd, "@CRITICAL_VALUE", tst.CRITICAL_VALUE);
                    SQLHelper.AddStringParameter(cmd, "@STORE_TEMP_PRE", tst.STORE_TEMP_PRE);
                    SQLHelper.AddStringParameter(cmd, "@STORE_TEMP_POST", tst.STORE_TEMP_POST);
                    SQLHelper.AddStringParameter(cmd, "@MIN_VOL_LAB", tst.MIN_VOL_LAB);
                    SQLHelper.AddDecimalParameter(cmd, "@PRICE_ESTIMATE", 12, 2, tst.PRICE_ESTIMATE);
                    SQLHelper.AddDecimalParameter(cmd, "@PRICE_UPPER", 12, 2, tst.PRICE_UPPER);
                    SQLHelper.AddStringParameter(cmd, "@CLINICAL_DESCRIPTION", tst.CLINICAL_DESCRIPTION);
                    SQLHelper.AddStringParameter(cmd, "@DOCREFERENCES", tst.DOCREFERENCES);
                    SQLHelper.AddStringParameter(cmd, "@WEB_REF_RANGE", tst.WEB_REF_RANGE);
                    SQLHelper.AddDecimalParameter(cmd, "@REF_CONV_ID", 18, 0, tst.REF_CONV_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@METHOD_ID", 18, 0, tst.METHOD_ID);
                    SQLHelper.AddStringParameter(cmd, "@COMPONENTS", tst.COMPONENTS);
                    SQLHelper.AddDecimalParameter(cmd, "@SHIPPING_ID", 18, 0, tst.SHIPPING_ID);
                    SQLHelper.AddStringParameter(cmd, "@COLLECTION_INSTRUCTIONS", tst.COLLECTION_INSTRUCTIONS);
                    SQLHelper.AddDecimalParameter(cmd, "@TESTING_SCHEDULE_ID", 18, 0, tst.TESTING_SCHEDULE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@TESTING_SCHEDULE2_ID", 18, 0, tst.TESTING_SCHEDULE2_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@WEB_TAT_ID", 18, 0, tst.WEB_TAT_ID);
                    SQLHelper.AddStringParameter(cmd, "@LOINC_CODE", tst.LOINC_CODE);
                    SQLHelper.AddStringParameter(cmd, "@LIS_CODE", tst.LIS_CODE);
                    SQLHelper.AddStringParameter(cmd, "@WEBVOLUME_REC", tst.WEBVOLUME_REC);
                    SQLHelper.AddStringParameter(cmd, "@WEBVOLUME_REQ", tst.WEBVOLUME_REQ);
                    SQLHelper.AddDecimalParameter(cmd, "@INTERNATIONAL_UNIT_ID", 18, 0, tst.INTERNATIONAL_UNIT_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@SPECIMEN_TYPE_ID", 18, 0, tst.SPECIMEN_TYPE_ID);
                    SQLHelper.AddDecimalParameter(cmd, "@WEBDISPLAY", 38, 0, tst.WEBDISPLAY);
                    SQLHelper.AddDecimalParameter(cmd, "@WEBDISCLAIMER_ID", 18, 0, tst.WEBDISCLAIMER_ID);
                    SQLHelper.AddStringParameter(cmd, "@SYNONYM1", tst.SYNONYM1);
                    SQLHelper.AddStringParameter(cmd, "@SYNONYM2", tst.SYNONYM2);
                    //SQLHelper.AddStringParameter(cmd, "@SOURCE", tst.SOURCE);
                    SQLHelper.AddDecimalParameter(cmd, "@DISPLAYINTERNAL", 38, 0, tst.DISPLAYINTERNAL);
                    SQLHelper.AddDecimalParameter(cmd, "@GRANTPRICE", 12, 2, tst.GRANTPRICE);

                    con.Open();
                    cmd.ExecuteNonQuery();
                   // tst.TEST_ID = Decimal.Parse(pTEST_ID.Value.ToString());
                }
            }
        }


        public static DataSet GetDDLItems(string itemName)
        {
            DataSet myds = new DataSet();
            
            using (SqlConnection conn = SQLHelper.GetConnection())
            {

                    conn.Open();
                
                    SqlDataAdapter myadapter = new SqlDataAdapter("Select * from " + itemName, conn);
                    //SqlDataAdapter myadapter = new SqlDataAdapter(cmd);

                    myadapter.Fill(myds);
            }
            return myds;
        }

        public static SqlDataReader GetLabTests()
        {
            SqlDataReader rdr;
            using (SqlConnection conn = SQLHelper.GetConnection())
            {

                using (SqlCommand cmd = new SqlCommand("dbo.get_LabTests_select"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
                    conn.Open();
                    cmd.Connection = conn;

                    rdr = cmd.ExecuteReader();

                    /*
             * while (rdr.Read())
            {
                int contactID = rdr.GetInt32(rdr.GetOrdinal("ContactID"));
                string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
                Response.Write(firstName + " (" + contactID.ToString() + ")");
            }

            rdr.Close();
                         */
                    
                }
            }

            
            return rdr;
        }

        public static void InsertEntry(LabTest tst)
        {
            using (IDbConnection con = SQLHelper.GetConnection())
            {
                using (IDbCommand cmd = SQLHelper.CreateCommand(con, "up_Entries_ins"))
                {
                    /*
                     * IDbDataParameter cID = SQLHelper.AddStringParameter(cmd, "@ContestantId", tst.ID);
                    SQLHelper.AddInt32Parameter(cmd, "@GameId",1);
                    SQLHelper.AddDateTimeParameter(cmd, "@Date", DateTime.Now);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    */

                    
                     //tst.ID = new Guid(pID.Value.ToString());
                         ;
                }
            }
        }

        public static void CheckEntry(string strEmail)
        {
            using (IDbConnection con = SQLHelper.GetConnection())
            {
                using (IDbCommand cmd = SQLHelper.CreateCommand(con, "GetCountByEmail"))
                {
                    SQLHelper.AddStringParameter(cmd, "@Email", strEmail);
                    IDbDataParameter cntEmail = SQLHelper.AddInt32Parameter(cmd, "@EmailCount", ParameterDirection.Output, 0);
                    //SQLHelper.AddInt32Parameter(cmd, "@EmailCount", ParameterDirection.Output, 0);
                    
                    con.Open();
                    cmd.Connection = con;

                    cmd.ExecuteNonQuery();
                    int count = Int32.Parse(cntEmail.Value.ToString());
                    if (count > 0)
                        throw new Exception("EMAIL_COUNT_NOT_ZERO");
                }
            }
        }

        public static LabTest GetLabTest(decimal decLabTestID)
        {
            SqlDataReader rdr;
            LabTest lt = new LabTest();
            using (SqlConnection conn = SQLHelper.GetConnection())
            {

                using (SqlCommand cmd = new SqlCommand("dbo.get_LabTest_select"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SQLHelper.AddDecimalParameter(cmd, "@TEST_ID", 18, 0, decLabTestID);
                    conn.Open();
                    cmd.Connection = conn;

                    rdr = cmd.ExecuteReader();
                    if (rdr.Read())
                    {

                        lt.TEST_ID = Utility.CheckNullDecimal(rdr["TEST_ID"].ToString());
                        lt.TEST = Convert.ToString(rdr["TEST"]);
                        lt.DIVISION_ID = Utility.CheckNullDecimal(rdr["DIVISION_ID"].ToString());
                        lt.FUNC_AREA_ID = Utility.CheckNullDecimal(rdr["FUNC_AREA_ID"].ToString());
                        lt.SPEC_TYPE_ID = Utility.CheckNullDecimal(rdr["SPEC_TYPE_ID"].ToString());
                        lt.CONTAINER_ID = Utility.CheckNullDecimal(rdr["CONTAINER_ID"].ToString());
                        lt.ALT_CONTAINER = Convert.ToString(rdr["ALT_CONTAINER"]);
                        lt.PATIENT_PREP = Convert.ToString(rdr["PATIENT_PREP"]);
                        lt.MIN_VOL_WARD = Convert.ToString(rdr["MIN_VOL_WARD"]);
                        lt.ULT_REQ_ID = Utility.CheckNullDecimal(rdr["ULT_REQ_ID"].ToString());
                        lt.DOWN_REQ_ID = Utility.CheckNullDecimal(rdr["DOWN_REQ_ID"].ToString());
                        lt.SUBMIT_SPEC = Convert.ToString(rdr["SUBMIT_SPEC"]);
                        lt.INFO_CALL = Convert.ToString(rdr["INFO_CALL"]);
                        lt.SPEC_HAND = Convert.ToString(rdr["SPEC_HAND"]);
                        lt.INSTRUMENT = Convert.ToString(rdr["INSTRUMENT"]);
                        lt.REF_OUT = Convert.ToString(rdr["REF_OUT"]);
                        lt.TAT_STAT = Convert.ToString(rdr["TAT_STAT"]);
                        lt.REF_RANGE = Convert.ToString(rdr["REF_RANGE"]);
                        lt.CRITICAL_VALUE = Convert.ToString(rdr["CRITICAL_VALUE"]);
                        lt.STORE_TEMP_PRE = Convert.ToString(rdr["STORE_TEMP_PRE"]);
                        lt.STORE_TEMP_POST = Convert.ToString(rdr["STORE_TEMP_POST"]);
                        lt.MIN_VOL_LAB = Convert.ToString(rdr["MIN_VOL_LAB"]);
                        lt.PRICE_ESTIMATE = Utility.CheckNullDecimal(rdr["PRICE_ESTIMATE"].ToString());
                        lt.PRICE_UPPER = Utility.CheckNullDecimal(rdr["PRICE_UPPER"].ToString());
                        lt.CLINICAL_DESCRIPTION = Convert.ToString(rdr["CLINICAL_DESCRIPTION"]);
                        lt.DOCREFERENCES = Convert.ToString(rdr["DOCREFERENCES"]);
                        lt.WEB_REF_RANGE = Convert.ToString(rdr["WEB_REF_RANGE"]);
                        lt.REF_CONV_ID = Utility.CheckNullDecimal(rdr["REF_CONV_ID"].ToString());
                        lt.METHOD_ID = Utility.CheckNullDecimal(rdr["METHOD_ID"].ToString());
                        lt.COMPONENTS = Convert.ToString(rdr["COMPONENTS"]);
                        lt.SHIPPING_ID = Utility.CheckNullDecimal(rdr["SHIPPING_ID"].ToString());
                        lt.COLLECTION_INSTRUCTIONS = Convert.ToString(rdr["COLLECTION_INSTRUCTIONS"]);
                        lt.TESTING_SCHEDULE_ID = Utility.CheckNullDecimal(rdr["TESTING_SCHEDULE_ID"].ToString());
                        lt.TESTING_SCHEDULE2_ID = Utility.CheckNullDecimal(rdr["TESTING_SCHEDULE2_ID"].ToString());
                        lt.WEB_TAT_ID = Utility.CheckNullDecimal(rdr["WEB_TAT_ID"].ToString());
                        lt.LOINC_CODE = Convert.ToString(rdr["LOINC_CODE"]);
                        lt.LIS_CODE = Convert.ToString(rdr["LIS_CODE"]);
                        lt.WEBVOLUME_REC = Convert.ToString(rdr["WEBVOLUME_REC"]);
                        lt.WEBVOLUME_REQ = Convert.ToString(rdr["WEBVOLUME_REQ"]);
                        lt.INTERNATIONAL_UNIT_ID = Utility.CheckNullDecimal(rdr["INTERNATIONAL_UNIT_ID"].ToString());
                        lt.SPECIMEN_TYPE_ID = Utility.CheckNullDecimal(rdr["SPECIMEN_TYPE_ID"].ToString());
                        lt.WEBDISPLAY = Utility.CheckNullDecimal(rdr["WEBDISPLAY"].ToString());
                        lt.WEBDISCLAIMER_ID = Utility.CheckNullDecimal(rdr["WEBDISCLAIMER_ID"].ToString());
                        lt.SYNONYM1 = Convert.ToString(rdr["SYNONYM1"]);
                        lt.SYNONYM2 = Convert.ToString(rdr["SYNONYM2"]);
                        //lt.SOURCE = Convert.ToString(rdr["SOURCE"]);
                        lt.DISPLAYINTERNAL = Utility.CheckNullDecimal(rdr["DISPLAYINTERNAL"].ToString());
                        lt.GRANTPRICE = Utility.CheckNullDecimal(rdr["GRANTPRICE"].ToString());

                    }


                }
            }

           return lt;
        }

        
        
        
        private object CheckNull(SqlDataReader rdr, string name)
        {
            if (DBNull.Value == rdr[name])
            {
                return null;
            }
            else
            {
                return rdr[name];
            }
        }
      
    }
}
